# -*- coding: utf-8 -*-
'''
@author: Script Team
@Created on: 2011-8-1
@version: 2011.8.1
@summary: Definition of ExcelOperator class
'''
import os
import win32com.client
import re
from action.step import Step


class ExcelOperator(object):
    '''
    Excel related operation.
    '''
    def __init__(self, file_path):
        '''
        Constructor.
        @params:
        file_path -- excel file path to be operated
        '''
        print 'exceloperator -- __init__ '
        try:
            self.excel_app = win32com.client.Dispatch("Excel.Application")
            self.file_path = file_path
        except:
            os.system('taskkill /fi "imagename eq excel.exe" /t')
            self.excel_app = win32com.client.Dispatch("Excel.Application")
            self.file_path = file_path  
           
    def get_sheet_list(self):
        '''
        Get sheet name list.
        '''
        print 'exceloperator -- get_sheet_list '
        try:
            wb = self.excel_app.Workbooks.Open(self.file_path)
            sheet_name_list = []
            str = ''
            for i in range(wb.Sheets.Count):
                name = wb.Sheets(i+1).Name
                m = re.search('_case', name, re.IGNORECASE)
                if bool(m):
                    sheet_name_list.append(name)
            return sheet_name_list
        except Exception, e:
            self.close()
            print 'exceloperator -- get_sheet_list - Exception: ' + str(e)         
            
    def get_case_id_list_by_priority(self, priority):
        '''
        Get caseId list by case priority.
        @params:
        priority -- case priority, e.g. "All", "High"
        '''
        print 'exceloperator -- get_case_id_list_by_priority '
        try:
            case_id_list = []
            sheet_name_list = self.get_sheet_list() 
            if len(sheet_name_list) > 0:
                for i in range(len(sheet_name_list)):
                    sheet_name = sheet_name_list[i]
                    wb = self.excel_app.Workbooks.Open(self.file_path)
                    ws = wb.Sheets(sheet_name)  
                    total_row_count = ws.UsedRange.Rows.Count
                    if priority != 'All':
                        for row in range(total_row_count):
                            if ws.Cells(row+2, 1).value \
                            and ws.Cells(row+2, 2).value == priority:  
                                case_id_list.append(ws.Cells(row+2, 1).value)  
                    else:
                        for row in range(total_row_count):
                            if ws.Cells(row+2, 1).value:  
                                case_id_list.append(ws.Cells(row+2, 1).value)  
            return case_id_list                              
        except Exception, e:
            self.close() 
            print 'exceloperator -- get_case_id_list_by_priority - ' \
                  + 'Exception: ' + str(e) 
             
    def get_step_info_list(self, case_id):
        '''
        Get step list.
        @params:
        case_id -- case id
        '''
        print 'exceloperator -- get_step_info_list '
        try:
            sheet_name_list = self.get_sheet_list() 
            is_continue = False
            
            if len(sheet_name_list) > 0:
                for i in range(len(sheet_name_list)):
                    sheet_name = sheet_name_list[i]
                    wb = self.excel_app.Workbooks.Open(self.file_path)
                    ws = wb.Sheets(sheet_name)  
                    total_row_count = ws.UsedRange.Rows.Count
                    step_info_list = []
                    
                    for row in range(total_row_count):
                        if str(ws.Cells(row+2, 1).value) == str(case_id) \
                        or is_continue:
                            action_value_list = []
                            if ws.Cells(row + 2, 3).value:
                                name = ws.Cells(row + 2, 3).value
                                key = ws.Cells(row + 2, 4).value
                                times = ws.Cells(row + 2, 5).value
                                description = ws.Cells(row + 2, 6).value
                                expected_result = ws.Cells(row + 2, 7).value                         
                                step_info = Step(name, key, times, 
                                                 description, expected_result)
                            action_name = ws.Cells(row + 2, 8).value
                            action_value_list.append(ws.Cells(row + 2, 
                                                              9).value)
                            action_value_list.append(ws.Cells(row + 2, 
                                                              10).value)                     
                            step_info.add_action(action_name, 
                                                 action_value_list)                          
                            if ws.Cells(row + 2, 11).value ==  'end':
                                step_info_list.append(step_info)
                            if ws.Cells(row + 2, 12).value == 'end':
                                is_continue = False
                                break 
                            else:
                                is_continue = True                                     
            return step_info_list                  
        except Exception,e:
            self.close()
            print 'exceloperator -- get_step_info_list - Exception: ' +  str(e) 
            
    def get_case_list(self, priority):
        '''
        Get case list.
        @params:
        priority -- case priority, e.g. "All", "High"
        '''
        print 'exceloperator -- get_case_list '
        try:
            case_id_list = []             
            case_id_list = self.get_case_id_list_by_priority(priority)
            case_info_list = []
            for case_id in case_id_list:
                temp_case_info_list = []
                step_info_list = self.get_step_info_list(case_id)
                temp_case_info_list.append(case_id)
                temp_case_info_list.append(step_info_list)
                case_info_list.append(temp_case_info_list)   
            return case_info_list    
        except Exception, e:
            self.close()
            print 'exceloperator -- get_case_list - Exception: ' + str(e)     
      
    def close(self):
        self.excel_app.Quit()
